﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;
using KPIS.GERP.GIMS.IDAL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class TreeDAL : IDALTree
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public TreeDAL()
        {

        }

        public IList<TreeInfo> GetResourceMasterTree()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT '0' AS ID, NULL AS PARENT_ID"
                        + ", 'จำแนกพัสดุ' AS NAME"
                        + ", NULL AS DESCR"
                        + ", '../Image/Tree/box.gif' AS IMAGE_URL"
                    + " UNION ALL"
                    + " SELECT CAST(ICM_RES_TYPE_SEQ AS CHAR) AS ID, '0' AS PARENT_ID"
                        + ", CONCAT(ICM_RES_TYPE_CODE, ' - ' , ICM_RES_TYPE_NAME) AS NAME"
                        + ", ICM_RES_TYPE_DESCR AS DESCR"
                        + ", '../Image/Tree/folder-1.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_TYPE"
                    + " WHERE RECORD_STATUS <> @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(ICM_RES_TYPE_SEQ, '-', ICM_RES_GRP_SEQ) AS CHAR)  AS ID"
                        + ", CAST(ICM_RES_TYPE_SEQ AS CHAR) AS PARENT_ID"
                        + ", CONCAT(ICM_RES_GRP_CODE, ' - ' , ICM_RES_GRP_NAME) AS NAME"
                        + ", ICM_RES_GRP_DESCR AS DESCR"
                        + ", '../Image/Tree/folder-2.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_GROUP"
                    + " WHERE RECORD_STATUS <> @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ) AS CHAR)  AS ID"
                        + ", CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ) AS CHAR) AS PARENT_ID"
                        + ", CONCAT(sg.ICM_RES_SUB_GRP_CODE, ' - ' , sg.ICM_RES_SUB_GRP_NAME) AS NAME"
                        + ", sg.ICM_RES_SUB_GRP_DESCR AS DESCR"
                        + ", '../Image/Tree/folder-3.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_SUB_GROUP sg"
                        + " INNER JOIN ICM_MAS_RESOURCE_GROUP g ON sg.ICM_RES_GRP_SEQ = g.ICM_RES_GRP_SEQ"
                    + " WHERE sg.RECORD_STATUS <> @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ, '-' , i.ICM_RES_ITEM_SEQ) AS CHAR)  AS ID"
                        + ", CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ) AS CHAR) AS PARENT_ID"
                        + ", CONCAT(i.ICM_RES_ITEM_CODE, ' - ' , i.ICM_RES_ITEM_NAME) AS NAME"
                        + ", i.ICM_RES_ITEM_DESCR AS DESCR"
                        + ", '../Image/Tree/folder-4.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_ITEM i"
                        + " INNER JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON sg.ICM_RES_SUB_GRP_SEQ = i.ICM_RES_SUB_GRP_SEQ"
                        + " INNER JOIN ICM_MAS_RESOURCE_GROUP g ON g.ICM_RES_GRP_SEQ = sg.ICM_RES_GRP_SEQ"
                    + " WHERE i.RECORD_STATUS <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCR"]) ? null : (dr[i]["DESCR"]).ToString();
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<TreeInfo> GetResourceMasterTree(string parent_id)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@parent_id", parent_id);

                strSQL = "SELECT main.* FROM "
                    + "(SELECT '0' AS ID, NULL AS PARENT_ID"
                        + ", 'จำแนกพัสดุ' AS NAME"
                        + ", NULL AS DESCR"
                        + ", (select count(icm_res_type_seq) from icm_mas_resource_type where record_status <> @delete_status) AS CHILDREN_COUNT"
                        + ", '../Image/Tree/box.gif' AS IMAGE_URL"
                    + " UNION ALL"
                    + " SELECT CAST(ICM_RES_TYPE_SEQ AS CHAR) AS ID, '0' AS PARENT_ID"
                        + ", CONCAT(ICM_RES_TYPE_CODE, ' - ' , ICM_RES_TYPE_NAME) AS NAME"
                        + ", ICM_RES_TYPE_DESCR AS DESCR"
                        + ", (select count(icm_res_type_seq) from icm_mas_resource_group where icm_res_type_seq = ICM_MAS_RESOURCE_TYPE.icm_res_type_seq) AS CHILDREN_COUNT"
                        + ", '../Image/Tree/folder-1.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_TYPE"
                    + " WHERE RECORD_STATUS <> @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(ICM_RES_TYPE_SEQ, '-', ICM_RES_GRP_SEQ) AS CHAR)  AS ID"
                        + ", CAST(ICM_RES_TYPE_SEQ AS CHAR) AS PARENT_ID"
                        + ", CONCAT(ICM_RES_GRP_CODE, ' - ' , ICM_RES_GRP_NAME) AS NAME"
                        + ", ICM_RES_GRP_DESCR AS DESCR"
                        + ", (select count(icm_res_grp_seq) from icm_mas_resource_sub_group where icm_res_grp_seq = ICM_MAS_RESOURCE_GROUP.icm_res_grp_seq) AS CHILDREN_COUNT"
                        + ", '../Image/Tree/folder-2.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_GROUP"
                    + " WHERE RECORD_STATUS <> @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ) AS CHAR)  AS ID"
                        + ", CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ) AS CHAR) AS PARENT_ID"
                        + ", CONCAT(sg.ICM_RES_SUB_GRP_CODE, ' - ' , sg.ICM_RES_SUB_GRP_NAME) AS NAME"
                        + ", sg.ICM_RES_SUB_GRP_DESCR AS DESCR"
                        + ", (select count(icm_res_item_seq) from icm_mas_resource_item where icm_res_sub_grp_seq = sg.icm_res_sub_grp_seq) AS CHILDREN_COUNT"
                        + ", '../Image/Tree/folder-3.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_SUB_GROUP sg"
                        + " INNER JOIN ICM_MAS_RESOURCE_GROUP g ON sg.ICM_RES_GRP_SEQ = g.ICM_RES_GRP_SEQ"
                    + " WHERE sg.RECORD_STATUS <> @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ, '-' , i.ICM_RES_ITEM_SEQ) AS CHAR)  AS ID"
                        + ", CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ) AS CHAR) AS PARENT_ID"
                        + ", CONCAT(i.ICM_RES_ITEM_CODE, ' - ' , i.ICM_RES_ITEM_NAME) AS NAME"
                        + ", i.ICM_RES_ITEM_DESCR AS DESCR"
                        + ", 0 AS CHILDREN_COUNT"
                        + ", '../Image/Tree/folder-4.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_ITEM i"
                        + " INNER JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON sg.ICM_RES_SUB_GRP_SEQ = i.ICM_RES_SUB_GRP_SEQ"
                        + " INNER JOIN ICM_MAS_RESOURCE_GROUP g ON g.ICM_RES_GRP_SEQ = sg.ICM_RES_GRP_SEQ"
                    + " WHERE i.RECORD_STATUS <> @delete_status"
                    + ") main"
                    + " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCR"]) ? null : (dr[i]["DESCR"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<TreeInfo> GetResourceMasterTreeResultSearch(string text_search)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@text_search", text_search);

                strSQL = "SELECT '0' AS ID, NULL AS PARENT_ID"
                        + ", 'จำแนกพัสดุ' AS NAME"
                        + ", NULL AS DESCR"
                        + ", '../Image/Tree/box.gif' AS IMAGE_URL"
                    + " UNION ALL"
                    + " SELECT DISTINCT main.* FROM "
                    + "(SELECT CAST(t.ICM_RES_TYPE_SEQ AS CHAR) AS ID, '0' AS PARENT_ID"
                        + ", CONCAT(t.ICM_RES_TYPE_CODE, ' - ' , t.ICM_RES_TYPE_NAME) AS NAME"
                        + ", t.ICM_RES_TYPE_DESCR AS DESCR"
                        + ", '../Image/Tree/folder-1.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_TYPE t"
                        + " LEFT JOIN ICM_MAS_RESOURCE_GROUP g ON g.ICM_RES_TYPE_SEQ = t.ICM_RES_TYPE_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON  g.ICM_RES_GRP_SEQ = sg.ICM_RES_GRP_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON sg.ICM_RES_SUB_GRP_SEQ = i.ICM_RES_SUB_GRP_SEQ"
                    + " WHERE t.RECORD_STATUS <> @delete_status"
                        + " AND (t.ICM_RES_TYPE_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR t.ICM_RES_TYPE_NAME LIKE CONCAT('%', @text_search , '%')"
                        + " OR g.ICM_RES_GRP_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR g.ICM_RES_GRP_NAME LIKE CONCAT('%', @text_search , '%')"
                        + " OR sg.ICM_RES_SUB_GRP_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR sg.ICM_RES_SUB_GRP_NAME LIKE CONCAT('%', @text_search , '%')"
                        + " OR i.ICM_RES_ITEM_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR i.ICM_RES_ITEM_NAME LIKE CONCAT('%', @text_search , '%')"
                        + ")"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(t.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ) AS CHAR)  AS ID"
                        + ", CAST(g.ICM_RES_TYPE_SEQ AS CHAR) AS PARENT_ID"
                        + ", CONCAT(g.ICM_RES_GRP_CODE, ' - ' , g.ICM_RES_GRP_NAME) AS NAME"
                        + ", g.ICM_RES_GRP_DESCR AS DESCR"
                        + ", '../Image/Tree/folder-2.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_TYPE t"
                        + " LEFT JOIN ICM_MAS_RESOURCE_GROUP g ON g.ICM_RES_TYPE_SEQ = t.ICM_RES_TYPE_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON  g.ICM_RES_GRP_SEQ = sg.ICM_RES_GRP_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON sg.ICM_RES_SUB_GRP_SEQ = i.ICM_RES_SUB_GRP_SEQ"
                    + " WHERE g.RECORD_STATUS <> @delete_status"
                        + " AND (t.ICM_RES_TYPE_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR t.ICM_RES_TYPE_NAME LIKE CONCAT('%', @text_search , '%')"
                        + " OR g.ICM_RES_GRP_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR g.ICM_RES_GRP_NAME LIKE CONCAT('%', @text_search , '%')"
                        + " OR sg.ICM_RES_SUB_GRP_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR sg.ICM_RES_SUB_GRP_NAME LIKE CONCAT('%', @text_search , '%')"
                        + " OR i.ICM_RES_ITEM_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR i.ICM_RES_ITEM_NAME LIKE CONCAT('%', @text_search , '%')"
                        + ")"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(t.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ) AS CHAR)  AS ID"
                        + ", CAST(CONCAT(t.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ) AS CHAR) AS PARENT_ID"
                        + ", CONCAT(sg.ICM_RES_SUB_GRP_CODE, ' - ' , sg.ICM_RES_SUB_GRP_NAME) AS NAME"
                        + ", sg.ICM_RES_SUB_GRP_DESCR AS DESCR"
                        + ", '../Image/Tree/folder-3.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_TYPE t"
                        + " LEFT JOIN ICM_MAS_RESOURCE_GROUP g ON g.ICM_RES_TYPE_SEQ = t.ICM_RES_TYPE_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON  g.ICM_RES_GRP_SEQ = sg.ICM_RES_GRP_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON sg.ICM_RES_SUB_GRP_SEQ = i.ICM_RES_SUB_GRP_SEQ"
                    + " WHERE sg.RECORD_STATUS <> @delete_status"
                        + " AND (t.ICM_RES_TYPE_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR t.ICM_RES_TYPE_NAME LIKE CONCAT('%', @text_search , '%')"
                        + " OR g.ICM_RES_GRP_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR g.ICM_RES_GRP_NAME LIKE CONCAT('%', @text_search , '%')"
                        + " OR sg.ICM_RES_SUB_GRP_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR sg.ICM_RES_SUB_GRP_NAME LIKE CONCAT('%', @text_search , '%')"
                        + " OR i.ICM_RES_ITEM_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR i.ICM_RES_ITEM_NAME LIKE CONCAT('%', @text_search , '%')"
                        + ")"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(t.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ, '-' , i.ICM_RES_ITEM_SEQ) AS CHAR)  AS ID"
                        + ", CAST(CONCAT(t.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ) AS CHAR) AS PARENT_ID"
                        + ", CONCAT(i.ICM_RES_ITEM_CODE, ' - ' , i.ICM_RES_ITEM_NAME) AS NAME"
                        + ", i.ICM_RES_ITEM_DESCR AS DESCR"
                        + ", '../Image/Tree/folder-4.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_TYPE t"
                        + " LEFT JOIN ICM_MAS_RESOURCE_GROUP g ON g.ICM_RES_TYPE_SEQ = t.ICM_RES_TYPE_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON  g.ICM_RES_GRP_SEQ = sg.ICM_RES_GRP_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON sg.ICM_RES_SUB_GRP_SEQ = i.ICM_RES_SUB_GRP_SEQ"
                    + " WHERE i.RECORD_STATUS <> @delete_status"
                        + " AND (t.ICM_RES_TYPE_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR t.ICM_RES_TYPE_NAME LIKE CONCAT('%', @text_search , '%')"
                        + " OR g.ICM_RES_GRP_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR g.ICM_RES_GRP_NAME LIKE CONCAT('%', @text_search , '%')"
                        + " OR sg.ICM_RES_SUB_GRP_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR sg.ICM_RES_SUB_GRP_NAME LIKE CONCAT('%', @text_search , '%')"
                        + " OR i.ICM_RES_ITEM_CODE LIKE CONCAT('%', @text_search , '%')"
                        + " OR i.ICM_RES_ITEM_NAME LIKE CONCAT('%', @text_search , '%')"
                        + ")"
                    + ") main";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCR"]) ? null : (dr[i]["DESCR"]).ToString();
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }


        public IList<TreeInfo> GetResourceMasterTreeByTypeCode(int TypeCode)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@text_search", TypeCode);

                strSQL = "SELECT '0' AS ID, NULL AS PARENT_ID"
                        + ", 'จำแนกพัสดุ' AS NAME"
                        + ", NULL AS DESCR"
                        + ", '../Image/Tree/box.gif' AS IMAGE_URL"
                    + " UNION ALL"
                    + " SELECT DISTINCT main.* FROM "
                    + "(SELECT CAST(t.ICM_RES_TYPE_SEQ AS CHAR) AS ID, '0' AS PARENT_ID"
                        + ", CONCAT(t.ICM_RES_TYPE_CODE, ' - ' , t.ICM_RES_TYPE_NAME) AS NAME"
                        + ", t.ICM_RES_TYPE_DESCR AS DESCR"
                        + ", '../Image/Tree/folder-1.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_TYPE t"
                        + " LEFT JOIN ICM_MAS_RESOURCE_GROUP g ON g.ICM_RES_TYPE_SEQ = t.ICM_RES_TYPE_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON  g.ICM_RES_GRP_SEQ = sg.ICM_RES_GRP_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON sg.ICM_RES_SUB_GRP_SEQ = i.ICM_RES_SUB_GRP_SEQ"
                    + " WHERE t.RECORD_STATUS <> @delete_status"
                        + " AND (t.ICM_RES_TYPE_CODE = @text_search "
                        + " OR i.ICM_RES_ITEM_CODE = @text_search)"
                      
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(t.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ) AS CHAR)  AS ID"
                        + ", CAST(g.ICM_RES_TYPE_SEQ AS CHAR) AS PARENT_ID"
                        + ", CONCAT(g.ICM_RES_GRP_CODE, ' - ' , g.ICM_RES_GRP_NAME) AS NAME"
                        + ", g.ICM_RES_GRP_DESCR AS DESCR"
                        + ", '../Image/Tree/folder-2.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_TYPE t"
                        + " LEFT JOIN ICM_MAS_RESOURCE_GROUP g ON g.ICM_RES_TYPE_SEQ = t.ICM_RES_TYPE_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON  g.ICM_RES_GRP_SEQ = sg.ICM_RES_GRP_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON sg.ICM_RES_SUB_GRP_SEQ = i.ICM_RES_SUB_GRP_SEQ"
                    + " WHERE g.RECORD_STATUS <> @delete_status"
                        + " AND (t.ICM_RES_TYPE_CODE = @text_search "
                        + " OR i.ICM_RES_ITEM_CODE = @text_search)"
                     
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(t.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ) AS CHAR)  AS ID"
                        + ", CAST(CONCAT(t.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ) AS CHAR) AS PARENT_ID"
                        + ", CONCAT(sg.ICM_RES_SUB_GRP_CODE, ' - ' , sg.ICM_RES_SUB_GRP_NAME) AS NAME"
                        + ", sg.ICM_RES_SUB_GRP_DESCR AS DESCR"
                        + ", '../Image/Tree/folder-3.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_TYPE t"
                        + " LEFT JOIN ICM_MAS_RESOURCE_GROUP g ON g.ICM_RES_TYPE_SEQ = t.ICM_RES_TYPE_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON  g.ICM_RES_GRP_SEQ = sg.ICM_RES_GRP_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON sg.ICM_RES_SUB_GRP_SEQ = i.ICM_RES_SUB_GRP_SEQ"
                    + " WHERE sg.RECORD_STATUS <> @delete_status"
                        + " AND (t.ICM_RES_TYPE_CODE = @text_search "
                        + " OR i.ICM_RES_ITEM_CODE = @text_search )"
    
                     
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(t.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ, '-' , i.ICM_RES_ITEM_SEQ) AS CHAR)  AS ID"
                        + ", CAST(CONCAT(t.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ) AS CHAR) AS PARENT_ID"
                        + ", CONCAT(i.ICM_RES_ITEM_CODE, ' - ' , i.ICM_RES_ITEM_NAME) AS NAME"
                        + ", i.ICM_RES_ITEM_DESCR AS DESCR"
                        + ", '../Image/Tree/folder-4.gif' AS IMAGE_URL"
                    + " FROM ICM_MAS_RESOURCE_TYPE t"
                        + " LEFT JOIN ICM_MAS_RESOURCE_GROUP g ON g.ICM_RES_TYPE_SEQ = t.ICM_RES_TYPE_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON  g.ICM_RES_GRP_SEQ = sg.ICM_RES_GRP_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON sg.ICM_RES_SUB_GRP_SEQ = i.ICM_RES_SUB_GRP_SEQ"
                    + " WHERE i.RECORD_STATUS <> @delete_status"
                        + " AND (t.ICM_RES_TYPE_CODE = @text_search "
                        + " OR i.ICM_RES_ITEM_CODE = @text_search)"

                    + ") main";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCR"]) ? null : (dr[i]["DESCR"]).ToString();
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<TreeInfo> GetResourceMasterTreeWithoutHeader(string parent_id)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@parent_id", parent_id);

                strSQL = "SELECT main.* FROM "
                    + "(SELECT CAST(ICM_RES_TYPE_SEQ AS CHAR) AS ID, NULL AS PARENT_ID"
                        + ", CONCAT(ICM_RES_TYPE_CODE, ' - ' , ICM_RES_TYPE_NAME) AS NAME"
                        + ", ICM_RES_TYPE_DESCR AS DESCR"
                        + ", (select count(icm_res_type_seq) from icm_mas_resource_group where icm_res_type_seq = ICM_MAS_RESOURCE_TYPE.icm_res_type_seq) AS CHILDREN_COUNT"
                        + ", '../Image/Tree/folder-1.gif' AS IMAGE_URL"
                        + ", 'TYPE' AS CATEGORY"
                    + " FROM ICM_MAS_RESOURCE_TYPE"
                    + " WHERE RECORD_STATUS <> @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(ICM_RES_TYPE_SEQ, '-', ICM_RES_GRP_SEQ) AS CHAR)  AS ID"
                        + ", CAST(ICM_RES_TYPE_SEQ AS CHAR) AS PARENT_ID"
                        + ", CONCAT(ICM_RES_GRP_CODE, ' - ' , ICM_RES_GRP_NAME) AS NAME"
                        + ", ICM_RES_GRP_DESCR AS DESCR"
                        + ", (select count(icm_res_grp_seq) from icm_mas_resource_sub_group where icm_res_grp_seq = ICM_MAS_RESOURCE_GROUP.icm_res_grp_seq) AS CHILDREN_COUNT"
                        + ", '../Image/Tree/folder-2.gif' AS IMAGE_URL"
                        + ", 'GROUP' AS CATEGORY"
                    + " FROM ICM_MAS_RESOURCE_GROUP"
                    + " WHERE RECORD_STATUS <> @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ) AS CHAR)  AS ID"
                        + ", CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ) AS CHAR) AS PARENT_ID"
                        + ", CONCAT(sg.ICM_RES_SUB_GRP_CODE, ' - ' , sg.ICM_RES_SUB_GRP_NAME) AS NAME"
                        + ", sg.ICM_RES_SUB_GRP_DESCR AS DESCR"
                        + ", (select count(icm_res_item_seq) from icm_mas_resource_item where icm_res_sub_grp_seq = sg.icm_res_sub_grp_seq) AS CHILDREN_COUNT"
                        + ", '../Image/Tree/folder-3.gif' AS IMAGE_URL"
                        + ", 'SUBGROUP' AS CATEGORY"
                    + " FROM ICM_MAS_RESOURCE_SUB_GROUP sg"
                        + " INNER JOIN ICM_MAS_RESOURCE_GROUP g ON sg.ICM_RES_GRP_SEQ = g.ICM_RES_GRP_SEQ"
                    + " WHERE sg.RECORD_STATUS <> @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ, '-' , i.ICM_RES_ITEM_SEQ) AS CHAR)  AS ID"
                        + ", CAST(CONCAT(g.ICM_RES_TYPE_SEQ, '-', g.ICM_RES_GRP_SEQ, '-' , sg.ICM_RES_SUB_GRP_SEQ) AS CHAR) AS PARENT_ID"
                        + ", CONCAT(i.ICM_RES_ITEM_CODE, ' - ' , i.ICM_RES_ITEM_NAME) AS NAME"
                        + ", u.ICM_UNIT_NAME AS DESCR"
                        + ", 0 AS CHILDREN_COUNT"
                        + ", '../Image/Tree/folder-4.gif' AS IMAGE_URL"
                        + ", 'ITEM' AS CATEGORY"
                    + " FROM ICM_MAS_RESOURCE_ITEM i"
                        + " INNER JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON sg.ICM_RES_SUB_GRP_SEQ = i.ICM_RES_SUB_GRP_SEQ"
                        + " INNER JOIN ICM_MAS_RESOURCE_GROUP g ON g.ICM_RES_GRP_SEQ = sg.ICM_RES_GRP_SEQ"
                        + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ"
                    + " WHERE i.RECORD_STATUS <> @delete_status"
                    + ") main"
                    + " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END"
                    + " ORDER BY NAME";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCR"]) ? null : (dr[i]["DESCR"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();
                        info.Category = (dr[i]["CATEGORY"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<TreeInfo> GetJobSiteTree()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT DISTINCT CAST(wa.SCOPE_SEQ AS CHAR) AS ID"
                            + ", NULL AS PARENT_ID"
                            + ", ms.SCOPE_NAME AS NAME"
                             + ", '../Image/Icon/child_building_icon.gif' AS IMAGE_URL"
                            + ", 'SCOPE' AS CATEGORY"
                        + " FROM HRM_MP_JOB_SITES js"
                            + " INNER JOIN HRM_MP_JOB_SITE_TYPES jst ON js.JOB_SITE_TYPE_SEQ = jst.JOB_SITE_TYPE_SEQ"
                            + " INNER JOIN HRM_MP_WORK_AREAS wa ON js.WORK_AREA_SEQ = wa.WORK_AREA_SEQ"
                            + " INNER JOIN HRM_MP_SCOPES ms ON wa.SCOPE_SEQ = ms.SCOPE_SEQ"
                        + " WHERE js.RECORD_STATUS <> @delete_status"
                            + " AND jst.JOB_SITE_TYPE_SEQ = 2"
                            + " AND js.JOB_SITE_UPPER_SEQ IS NOT NULL"
                            + " AND js.JOB_SITE_FULL_PATH LIKE concat('%/', jst.CURR_JOB_SITE_SEQ, '/%')"
                        + " UNION ALL"
                        + " SELECT CAST(CONCAT(wa.SCOPE_SEQ, '-', js.JOB_SITE_SEQ) AS CHAR) AS ID"
                            + ", CASE WHEN js.JOB_SITE_UPPER_SEQ = jst.CURR_JOB_SITE_SEQ"
                                + " THEN CAST(wa.SCOPE_SEQ AS CHAR)"
                                + " ELSE CAST(CONCAT(wa.SCOPE_SEQ, '-', js.JOB_SITE_SEQ) AS CHAR)"
                            + " END AS PARENT_ID"
                            + ", js.JOB_SITE_NAME AS NAME"
                             + ", '../Image/Icon/child_building_icon.gif' AS IMAGE_URL"
                            + ", 'JOBSITE' AS CATEGORY"
                        + " FROM HRM_MP_JOB_SITES js"
                            + " INNER JOIN HRM_MP_JOB_SITE_TYPES jst ON js.JOB_SITE_TYPE_SEQ = jst.JOB_SITE_TYPE_SEQ"
                            + " INNER JOIN HRM_MP_WORK_AREAS wa ON js.WORK_AREA_SEQ = wa.WORK_AREA_SEQ"
                            + " INNER JOIN HRM_MP_SCOPES ms ON wa.SCOPE_SEQ = ms.SCOPE_SEQ"
                        + " WHERE js.RECORD_STATUS <> @delete_status"
                            + " AND jst.JOB_SITE_TYPE_SEQ = 2"
                            + " AND js.JOB_SITE_UPPER_SEQ IS NOT NULL"
                            + " AND js.JOB_SITE_FULL_PATH LIKE concat('%/', jst.CURR_JOB_SITE_SEQ, '/%')";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();
                        info.Category = (dr[i]["CATEGORY"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<TreeInfo> GetJobSiteTreeOnDemand(string parent_id)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@parent_id", parent_id);

                strSQL = "SELECT main.* FROM "
                        + "(SELECT CAST(js.JOB_SITE_SEQ AS CHAR) AS ID"
                            + ", NULL AS PARENT_ID"
                            + ", js.JOB_SITE_NAME AS NAME"
                            + ", '../Image/Icon/child_building_icon.gif' AS IMAGE_URL"
                            + ", (select count(icm_res_item_seq) from icm_mas_resource_item where icm_res_sub_grp_seq = sg.icm_res_sub_grp_seq) AS CHILDREN_COUNT"
                            + ", 'HEAD' AS CATEGORY"
                        + " FROM HRM_MP_JOB_SITES js"
                            + " INNER JOIN HRM_MP_JOB_SITE_TYPES jst ON js.JOB_SITE_TYPE_SEQ = jst.JOB_SITE_TYPE_SEQ"
                        + " WHERE js.RECORD_STATUS <> @delete_status"
                            + " AND jst.JOB_SITE_TYPE_SEQ = 2"
                            + " AND js.JOB_SITE_SEQ = jst.CURR_JOB_SITE_SEQ"
                        + " UNION ALL"
                        + " SELECT DISTINCT CAST(CONCAT(jst.CURR_JOB_SITE_SEQ, '-', wa.SCOPE_SEQ) AS CHAR) AS ID"
                            + ", CAST(jst.CURR_JOB_SITE_SEQ AS CHAR) AS PARENT_ID"
                            + ", ms.SCOPE_NAME AS NAME"
                            + ", '../Image/Icon/child_building_icon.gif' AS IMAGE_URL"
                            + ", (select count(icm_res_item_seq) from icm_mas_resource_item where icm_res_sub_grp_seq = sg.icm_res_sub_grp_seq) AS CHILDREN_COUNT"
                            + ", 'SCOPE' AS CATEGORY"
                        + " FROM HRM_MP_JOB_SITES js"
                            + " INNER JOIN HRM_MP_JOB_SITE_TYPES jst ON js.JOB_SITE_TYPE_SEQ = jst.JOB_SITE_TYPE_SEQ"
                            + " INNER JOIN HRM_MP_WORK_AREAS wa ON js.WORK_AREA_SEQ = wa.WORK_AREA_SEQ"
                            + " INNER JOIN HRM_MP_SCOPES ms ON wa.SCOPE_SEQ = ms.SCOPE_SEQ"
                        + " WHERE js.RECORD_STATUS <> @delete_status"
                            + " AND jst.JOB_SITE_TYPE_SEQ = 2"
                            + " AND js.JOB_SITE_UPPER_SEQ IS NOT NULL"
                            + " AND js.JOB_SITE_FULL_PATH LIKE concat('%/', jst.CURR_JOB_SITE_SEQ, '/%')"
                        + " UNION ALL"
                        + " SELECT CAST(CONCAT(jst.CURR_JOB_SITE_SEQ, '-', wa.SCOPE_SEQ, '-', js.JOB_SITE_SEQ) AS CHAR) AS ID"
                            + ", CASE WHEN js.JOB_SITE_UPPER_SEQ = jst.CURR_JOB_SITE_SEQ"
                                + " THEN CAST(CONCAT(jst.CURR_JOB_SITE_SEQ, '-', wa.SCOPE_SEQ) AS CHAR)"
                                + " ELSE CAST(CONCAT(jst.CURR_JOB_SITE_SEQ, '-', wa.SCOPE_SEQ, '-', js.JOB_SITE_SEQ) AS CHAR)"
                            + " END AS PARENT_ID"
                            + ", js.JOB_SITE_NAME AS NAME"
                            + ", '../Image/Icon/child_building_icon.gif' AS IMAGE_URL"
                            + ", (select count(icm_res_item_seq) from icm_mas_resource_item where icm_res_sub_grp_seq = sg.icm_res_sub_grp_seq) AS CHILDREN_COUNT"
                            + ", 'JOBSITE' AS CATEGORY"
                        + " FROM HRM_MP_JOB_SITES js"
                            + " INNER JOIN HRM_MP_JOB_SITE_TYPES jst ON js.JOB_SITE_TYPE_SEQ = jst.JOB_SITE_TYPE_SEQ"
                            + " INNER JOIN HRM_MP_WORK_AREAS wa ON js.WORK_AREA_SEQ = wa.WORK_AREA_SEQ"
                            + " INNER JOIN HRM_MP_SCOPES ms ON wa.SCOPE_SEQ = ms.SCOPE_SEQ"
                        + " WHERE js.RECORD_STATUS <> @delete_status"
                            + " AND jst.JOB_SITE_TYPE_SEQ = 2"
                            + " AND js.JOB_SITE_UPPER_SEQ IS NOT NULL"
                            + " AND js.JOB_SITE_FULL_PATH LIKE concat('%/', jst.CURR_JOB_SITE_SEQ, '/%')"
                        + " ) main"
                        + " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END"
                        + " ORDER BY NAME";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCR"]) ? null : (dr[i]["DESCR"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Category = (dr[i]["CATEGORY"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
        public IList<TreeInfo> GetReceiveItemTree()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT '0' AS ID, NULL AS PARENT_ID"
                        + ", 'รายการพัสดุ' AS NAME"
                        //+ ", NULL AS DESCR"
                        + ", '../Image/Tree/box.gif' AS IMAGE_URL"
                        + " UNION ALL"
                        + " SELECT CAST(item.ICM_REC_ITEM_SEQ) AS CHAR) AS ID"
                            + ", CAST(item.UPPER_ICM_RC_ITEM_SEQ AS CHAR) AS PARENT_ID"
                            + ", CAST(CONCAT(item.SERIAL_NUMBER, ' - ', rsitem.ICM_RES_ITEM_NAME) AS CHAR) AS NAME"
                            + ", '../Image/Icon/child_building_icon.gif' AS IMAGE_URL"
                            //+ ", 'JOBSITE' AS CATEGORY"
                        + " FROM ICM_RECEIVE_ITEM item"
                            + " LEFT JOIN ICM_DELIVERY_NOTE_ITEM dnitem ON item.ICM_DN_ITEM_SEQ = dnitem.ICM_DN_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM rsitem ON item.ICM_RES_ITEM_SEQ = rsitem.ICM_RES_ITEM_SEQ"
                        + " WHERE item.RECORD_STATUS <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();
                        //info.Category = (dr[i]["CATEGORY"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        public IList<TreeInfo> GetReceiveItemTreeOnDemand(string parent_id, Nullable<int> ReceiveSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@parent_id", parent_id);
                dbManager.AddParameters(2, "@ReceiveSeq", ReceiveSeq);

                strSQL = "SELECT main.* FROM ("
                        + " SELECT '0' AS ID, NULL AS PARENT_ID"
                        + ", CAST(rc.ICM_RECEIVE_NAME AS CHAR) AS NAME"
                        + ", '../Image/Tree/box.gif' AS IMAGE_URL"
                        + ", (select count(icm_rc_item_seq) from icm_receive_item where icm_receive_seq = rc.icm_receive_seq) AS CHILDREN_COUNT"
                        + " FROM ICM_RECEIVE rc"
                        + " WHERE rc.ICM_RECEIVE_SEQ = @ReceiveSeq"
                        + " UNION ALL "
                        + " SELECT CAST(item.ICM_RC_ITEM_SEQ AS CHAR) AS ID"
                            + ", CASE WHEN item.UPPER_ICM_RC_ITEM_SEQ IS NULL THEN CAST('0' AS CHAR)"
                                + " ELSE CAST(item.UPPER_ICM_RC_ITEM_SEQ AS CHAR)"
                                + " END AS PARENT_ID"
                            + ", CAST(CONCAT(item.SERIAL_NUMBER, ' - ', rsitem.ICM_RES_ITEM_NAME) AS CHAR) AS NAME"
                            + ", '../Image/Icon/child_building_icon.gif' AS IMAGE_URL"
                            + ", (select count(icm_rc_item_seq) from icm_receive_item where upper_icm_rc_item_seq = item.icm_rc_item_seq) AS CHILDREN_COUNT"
                            //+ ", 'HEAD' AS CATEGORY"
                        + " FROM ICM_RECEIVE_ITEM item"
                            + " LEFT JOIN ICM_DELIVERY_NOTE_ITEM dnitem ON item.ICM_DN_ITEM_SEQ = dnitem.ICM_DN_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM rsitem ON item.ICM_RES_ITEM_SEQ = rsitem.ICM_RES_ITEM_SEQ"
                        + " WHERE item.RECORD_STATUS <> @delete_status"
                        + (ReceiveSeq.HasValue ? " AND item.ICM_RECEIVE_SEQ = @ReceiveSeq" : "")                    
                        + " ) main"
                        + " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END"
                        + " ORDER BY NAME";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        //info.Descr = Convert.IsDBNull(dr[i]["DESCR"]) ? null : (dr[i]["DESCR"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        //info.Category = (dr[i]["CATEGORY"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
        public IList<TreeInfo> GetWarehouseConsumeJobSiteTree(Nullable<int> warehouse_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@warehouse_seq", warehouse_seq);

                strSQL = "SELECT DISTINCT CAST(wa.SCOPE_SEQ AS CHAR) AS ID"
                            + ", NULL AS PARENT_ID"
                            + ", ms.SCOPE_NAME AS NAME"
                             + ", '../Image/Icon/building_icon.gif' AS IMAGE_URL"
                            + ", 'SCOPE' AS CATEGORY"
                        + " FROM HRM_MP_JOB_SITES js"
                            + " INNER JOIN HRM_MP_JOB_SITE_TYPES jst ON js.JOB_SITE_TYPE_SEQ = jst.JOB_SITE_TYPE_SEQ"
                            + " INNER JOIN HRM_MP_WORK_AREAS wa ON js.WORK_AREA_SEQ = wa.WORK_AREA_SEQ"
                            + " INNER JOIN HRM_MP_SCOPES ms ON wa.SCOPE_SEQ = ms.SCOPE_SEQ"
                        + " WHERE js.RECORD_STATUS <> @delete_status"
                            + " AND jst.JOB_SITE_TYPE_SEQ = 2"
                            + " AND js.JOB_SITE_UPPER_SEQ IS NOT NULL"
                            + " AND js.JOB_SITE_FULL_PATH LIKE concat('%/', jst.CURR_JOB_SITE_SEQ, '/%')"
                        + " UNION ALL"
                        + " SELECT CAST(CONCAT(wa.SCOPE_SEQ, '-', js.JOB_SITE_SEQ) AS CHAR) AS ID"
                            + ", CASE WHEN js.JOB_SITE_UPPER_SEQ = jst.CURR_JOB_SITE_SEQ"
                                + " THEN CAST(wa.SCOPE_SEQ AS CHAR)"
                                + " ELSE CAST(CONCAT(wa.SCOPE_SEQ, '-', js.JOB_SITE_SEQ) AS CHAR)"
                            + " END AS PARENT_ID"
                            + ", js.JOB_SITE_NAME AS NAME"
                             + ", '../Image/Icon/child_building_icon.gif' AS IMAGE_URL"
                            + ", 'JOBSITE' AS CATEGORY"
                        + " FROM HRM_MP_JOB_SITES js"
                            + " INNER JOIN HRM_MP_JOB_SITE_TYPES jst ON js.JOB_SITE_TYPE_SEQ = jst.JOB_SITE_TYPE_SEQ"
                            + " INNER JOIN HRM_MP_WORK_AREAS wa ON js.WORK_AREA_SEQ = wa.WORK_AREA_SEQ"
                            + " INNER JOIN HRM_MP_SCOPES ms ON wa.SCOPE_SEQ = ms.SCOPE_SEQ"
                        + " WHERE js.RECORD_STATUS <> @delete_status"
                            + " AND js.JOB_SITE_LEVEL_SEQ = 3"
                            + " AND jst.JOB_SITE_TYPE_SEQ = 2"
                            + " AND js.JOB_SITE_UPPER_SEQ IS NOT NULL"
                            + " AND js.JOB_SITE_FULL_PATH LIKE concat('%/', jst.CURR_JOB_SITE_SEQ, '/%')"
                            + " AND CASE WHEN @warehouse_seq IS NULL THEN 0 = 0"
                                + " ELSE js.JOB_SITE_SEQ NOT IN "
                                    + " (select job_site_seq from icm_mas_warehouse_consume where icm_wh_seq = @warehouse_seq)"
                            + " END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<TreeInfo> GetPurchaseMethodTree()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT CAST(ICM_PC_TYPE_SEQ AS CHAR) AS ID"
                            + ", NULL AS PARENT_ID"
                            + ", ICM_PC_TYPE_NAME AS NAME"
                            + ", ICM_PC_TYPE_DESCR AS DESCR"
                        + " FROM ICM_MAS_PURCHASE_TYPE"
                        + " WHERE RECORD_STATUS <> @delete_status"
                        + " UNION ALL"
                        + " SELECT CAST(CONCAT(ICM_PC_TYPE_SEQ , '-' , ICM_PC_METHOD_SEQ) AS CHAR) AS ID"
                            + ", CAST(ICM_PC_TYPE_SEQ AS CHAR) AS PARENT_ID"
                            + ", ICM_PC_METHOD_NAME AS NAME"
                            + ", ICM_PC_METHOD_DESCR AS DESCR"
                        + " FROM ICM_MAS_PURCHASE_METHOD"
                        + " WHERE RECORD_STATUS <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = (dr[i]["DESCR"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<TreeInfo> GetBgmExpensePlan()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT DISTINCT CAST(r.ICM_PC_RULE_SEQ AS CHAR) AS ID"
                            + ", NULL AS PARENT_ID"
                            + ", r.ICM_PC_RULE_NAME AS NAME"
                            + ", r.ICM_PC_RULE_DESCR AS DESCR"
                            + ", 'RULE' AS CATEGORY"
                        + " FROM ICM_MAS_PURCHASE_RULE_MAPPING rm"
                            + " INNER JOIN ICM_MAS_PURCHASE_RULE r ON rm.ICM_PC_RULE_SEQ = r.ICM_PC_RULE_SEQ"
                        + " WHERE rm.RECORD_STATUS <> @delete_status"
                        + " UNION ALL"
                        + " SELECT CAST(CONCAT(rm.ICM_PC_RULE_SEQ , '-', s.BGM_SOURCE_SEQ) AS CHAR) AS ID"
                            + ", CAST(rm.ICM_PC_RULE_SEQ AS CHAR) AS PARENT_ID"
                            + ", s.BGM_SOURCE_NAME AS NAME"
                            + ", s.BGM_SOURCE_DESCR AS DESCR"
                            + ", 'SOURCE' AS CATEGORY"
                        + " FROM ICM_MAS_PURCHASE_RULE_MAPPING rm"
                            + " INNER JOIN BGM_MAS_SOURCE s ON rm.BGM_SOURCE_SEQ = s.BGM_SOURCE_SEQ"
                        + " WHERE s.RECORD_STATUS <> @delete_status"
                        + " UNION ALL"
                        + " SELECT DISTINCT CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-', re.BGM_SOURCE_SEQ , '-' , re.BGM_REQ_SEQ) AS CHAR) AS ID"
                            + ", CAST(CONCAT(rm.ICM_PC_RULE_SEQ , '-', re.BGM_SOURCE_SEQ) AS CHAR) AS PARENT_ID"
                            + ", r.BGM_REQ_NAME AS NAME"
                            + ", r.BGM_REQ_DESCR AS DESCR"
                            + ", 'REQUEST' AS CATEGORY"
                        + " FROM BGM_REQUEST r"
                            + " INNER JOIN BGM_REQUEST_EXPENSE re ON r.BGM_REQ_SEQ = re.BGM_REQ_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_RULE_MAPPING rm ON re.BGM_SOURCE_SEQ = rm.BGM_SOURCE_SEQ"
                        + " WHERE r.RECORD_STATUS <> @delete_status"
                        + " UNION ALL"
                        + " SELECT CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-', re.BGM_SOURCE_SEQ , '-' , re.BGM_REQ_SEQ, '-' , re.BGM_REQ_EXP_SEQ) AS CHAR) AS ID"
                            + ", CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-', re.BGM_SOURCE_SEQ , '-' , re.BGM_REQ_SEQ) AS CHAR) AS PARENT_ID"
                            + ", ex.BGM_EXP_NAME AS NAME"
                            + ", re.BGM_REQ_EXP_DESCR AS DESCR"
                            + ", 'EXPENSE' AS CATEGORY"
                        + " FROM BGM_REQUEST_EXPENSE re"
                            + " INNER JOIN BGM_MAS_EXPENSE ex ON re.BGM_EXP_SEQ = ex.BGM_EXP_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_RULE_MAPPING rm ON re.BGM_SOURCE_SEQ = rm.BGM_SOURCE_SEQ";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = (dr[i]["DESCR"]).ToString();
                        info.Category = (dr[i]["CATEGORY"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<TreeInfo> GetBgmExpenseProject(int bgm_year, int job_site_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@job_site_seq", job_site_seq);

                strSQL = " SELECT DISTINCT CAST(r.ICM_PC_RULE_SEQ AS CHAR) AS ID "
                        + " , NULL AS PARENT_ID "
                        + " , r.ICM_PC_RULE_NAME AS NAME "
                        + " , r.ICM_PC_RULE_DESCR AS DESCR "
                        + " , 'RULE' AS CATEGORY "
                    + " FROM ICM_MAS_PURCHASE_RULE_MAPPING rm "
                        + " INNER JOIN ICM_MAS_PURCHASE_RULE r ON rm.ICM_PC_RULE_SEQ = r.ICM_PC_RULE_SEQ "
                    + " WHERE rm.RECORD_STATUS <> @delete_status"
                        + " AND r.RECORD_STATUS <> @delete_status"
                    //" UNION ALL " +
                    //" SELECT CAST(CONCAT(rm.ICM_PC_RULE_SEQ , '-', s.BGM_SOURCE_SEQ) AS CHAR) AS ID " +
                    //    " , CAST(rm.ICM_PC_RULE_SEQ AS CHAR) AS PARENT_ID " +
                    //    " , s.BGM_SOURCE_NAME AS NAME " +
                    //    " , s.BGM_SOURCE_DESCR AS DESCR " +
                    //    " , 'SOURCE' AS CATEGORY " +
                    //" FROM ICM_MAS_PURCHASE_RULE_MAPPING rm " +
                    //    " INNER JOIN BGM_MAS_SOURCE s ON rm.BGM_SOURCE_SEQ = s.BGM_SOURCE_SEQ " +
                    //" WHERE s.RECORD_STATUS <> 'D' " +
                    + " UNION ALL "
                    + " SELECT DISTINCT CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-', pe.BGM_PROJ_SEQ) AS CHAR) AS ID "
                        + " , CAST(rm.ICM_PC_RULE_SEQ AS CHAR) AS PARENT_ID "
                        + " , p.BGM_PROJ_NAME AS NAME "
                        + " , p.BGM_PROJ_DESCR AS DESCR "
                        + " , 'PROJECT' AS CATEGORY "
                    + " FROM BGM_PROJECT p "
                        + " INNER JOIN BGM_PROJECT_EXPENSE pe ON p.BGM_PROJ_SEQ = pe.BGM_PROJ_SEQ "
                        + " INNER JOIN ICM_MAS_PURCHASE_RULE_MAPPING rm ON pe.BGM_SOURCE_SEQ = rm.BGM_SOURCE_SEQ "
                    + " WHERE p.RECORD_STATUS = @approve_status"
                        + " AND p.BGM_YEAR = @bgm_year"
                        + " AND p.JOB_SITE_SEQ = @job_site_seq"
                    + " UNION ALL "
                    + " SELECT CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-', pe.BGM_SOURCE_SEQ , '-' , pe.BGM_PROJ_SEQ, '-' , pe.BGM_PROJ_EXP_SEQ) AS CHAR) AS ID "
                        + " , CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-', pe.BGM_PROJ_SEQ) AS CHAR) AS PARENT_ID "
                        + " , CONCAT('(' , s.BGM_SOURCE_NAME, ')', ex.BGM_EXP_NAME) AS NAME"
                        + " , pe.DESCR AS DESCR "
                        + " , 'EXPENSE' AS CATEGORY "
                    + " FROM BGM_PROJECT_EXPENSE pe "
                        + " INNER JOIN BGM_PROJECT p ON pe.BGM_PROJ_SEQ = p.BGM_PROJ_SEQ"
                        + " INNER JOIN BGM_MAS_EXPENSE ex ON pe.BGM_EXP_SEQ = ex.BGM_EXP_SEQ "
                        + " INNER JOIN BGM_MAS_SOURCE s ON pe.BGM_SOURCE_SEQ = s.BGM_SOURCE_SEQ "
                        + " INNER JOIN ICM_MAS_PURCHASE_RULE_MAPPING rm ON pe.BGM_SOURCE_SEQ = rm.BGM_SOURCE_SEQ "
                    + " WHERE p.RECORD_STATUS = @approve_status"
                        + " AND p.BGM_YEAR = @bgm_year"
                        + " AND p.JOB_SITE_SEQ = @job_site_seq"
                    + " UNION ALL "
                    + " SELECT CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-', pe.BGM_SOURCE_SEQ , '-' , pe.BGM_PROJ_SEQ, '-' , pe.BGM_PROJ_EXP_SEQ, '-' , i.ICM_RES_ITEM_SEQ) AS CHAR) AS ID "
                        + " , CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-', pe.BGM_SOURCE_SEQ , '-' , pe.BGM_PROJ_SEQ, '-' , pe.BGM_PROJ_EXP_SEQ) AS CHAR) AS PARENT_ID "
                        + " , i.ICM_RES_ITEM_NAME AS NAME "
                        + " , i.ICM_RES_ITEM_DESCR AS DESCR "
                        + " , 'ITEM' AS CATEGORY "
                    + " FROM BGM_PROJECT_EXPENSE pe "
                        + " INNER JOIN BGM_PROJECT p ON  pe.BGM_PROJ_SEQ = p.BGM_PROJ_SEQ"
                        + " INNER JOIN ICM_MAS_RESOURCE_ITEM i ON pe.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                        + " INNER JOIN ICM_MAS_PURCHASE_RULE_MAPPING rm ON pe.BGM_SOURCE_SEQ = rm.BGM_SOURCE_SEQ"
                    + " WHERE p.RECORD_STATUS = @approve_status"
                        + " AND p.BGM_YEAR = @bgm_year"
                        + " AND p.JOB_SITE_SEQ = @job_site_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = (dr[i]["DESCR"]).ToString();
                        info.Category = (dr[i]["CATEGORY"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<TreeInfo> GetBgmExpenseProjectOperation(int bgm_year, int job_site_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@job_site_seq", job_site_seq);

                strSQL = " SELECT DISTINCT CAST(r.ICM_PC_RULE_SEQ AS CHAR) AS ID "
                        + " , NULL AS PARENT_ID "
                        + " , r.ICM_PC_RULE_NAME AS NAME "
                        + " , r.ICM_PC_RULE_DESCR AS DESCR "
                        + " , 'RULE' AS CATEGORY "
                    + " FROM ICM_MAS_PURCHASE_RULE_MAPPING rm "
                        + " INNER JOIN ICM_MAS_PURCHASE_RULE r ON rm.ICM_PC_RULE_SEQ = r.ICM_PC_RULE_SEQ "
                    + " WHERE rm.RECORD_STATUS <> @delete_status"
                        + " AND r.RECORD_STATUS <> @delete_status"
                    + " UNION ALL "
                    + " SELECT DISTINCT CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-' , poe.BGM_PROJ_OPER_SEQ) AS CHAR) AS ID "
                        + " , CAST(rm.ICM_PC_RULE_SEQ AS CHAR) AS PARENT_ID "
                        + " , po.BGM_PROJ_OPER_NAME AS NAME "
                        + " , po.BGM_PROJ_OPER_DESCR AS DESCR "
                        + " , 'PROJECT' AS CATEGORY "
                    + " FROM BGM_PROJECT_OPERATION po "
                        + " INNER JOIN BGM_PROJECT_OPERATION_EXPENSE poe ON po.BGM_PROJ_OPER_SEQ = poe.BGM_PROJ_OPER_SEQ "
                        + " INNER JOIN BGM_PROJECT_EXPENSE pe ON poe.BGM_PROJ_EXP_SEQ = pe.BGM_PROJ_EXP_SEQ"
                        + " INNER JOIN ICM_MAS_PURCHASE_RULE_MAPPING rm ON pe.BGM_SOURCE_SEQ = rm.BGM_SOURCE_SEQ "
                    + " WHERE po.RECORD_STATUS = @approve_status"
                        + " AND po.BGM_YEAR = @bgm_year"
                        + " AND po.JOB_SITE_SEQ = @job_site_seq"
                    //" UNION ALL " +
                    //" SELECT DISTINCT CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-' , poe.BGM_PROJ_OPER_SEQ, '-', pe.BGM_SOURCE_SEQ) AS CHAR) AS ID " +
                    //    " , CAST(CONCAT(rm.ICM_PC_RULE_SEQ , '-', poe.BGM_PROJ_OPER_SEQ) AS CHAR) AS PARENT_ID " +
                    //    " , s.BGM_SOURCE_NAME AS NAME " +
                    //    " , s.BGM_SOURCE_DESCR AS DESCR " +
                    //    " , 'SOURCE' AS CATEGORY " +
                    //" FROM BGM_PROJECT_OPERATION po " +
                    //    " INNER JOIN BGM_PROJECT_OPERATION_EXPENSE poe ON po.BGM_PROJ_OPER_SEQ = poe.BGM_PROJ_OPER_SEQ " +
                    //    " INNER JOIN BGM_PROJECT_EXPENSE pe ON poe.BGM_PROJ_EXP_SEQ = pe.BGM_PROJ_EXP_SEQ" +
                    //    " INNER JOIN BGM_MAS_SOURCE s ON pe.BGM_SOURCE_SEQ = s.BGM_SOURCE_SEQ " +
                    //    " INNER JOIN ICM_MAS_PURCHASE_RULE_MAPPING rm ON pe.BGM_SOURCE_SEQ = rm.BGM_SOURCE_SEQ " +
                    //" WHERE po.RECORD_STATUS <> @delete_status" +
                    //" SELECT CAST(CONCAT(rm.ICM_PC_RULE_SEQ , '-', s.BGM_SOURCE_SEQ) AS CHAR) AS ID " +
                    //    " , CAST(rm.ICM_PC_RULE_SEQ AS CHAR) AS PARENT_ID " +
                    //    " , s.BGM_SOURCE_NAME AS NAME " +
                    //    " , s.BGM_SOURCE_DESCR AS DESCR " +
                    //    " , 'SOURCE' AS CATEGORY " +
                    //" FROM ICM_MAS_PURCHASE_RULE_MAPPING rm " +
                    //    " INNER JOIN BGM_MAS_SOURCE s ON rm.BGM_SOURCE_SEQ = s.BGM_SOURCE_SEQ " +
                    //" WHERE s.RECORD_STATUS <> @delete_status" +
                    + " UNION ALL "
                    + " SELECT CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-', pe.BGM_SOURCE_SEQ , '-' , poe.BGM_PROJ_OPER_SEQ, '-' , poe.BGM_PROJ_OPER_EXP_SEQ) AS CHAR) AS ID "
                        + " , CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-' , poe.BGM_PROJ_OPER_SEQ) AS CHAR) AS PARENT_ID "
                        + " , CONCAT('(' , s.BGM_SOURCE_NAME, ')', ex.BGM_EXP_NAME) AS NAME "
                        + " , pe.DESCR AS DESCR "
                        + " , 'EXPENSE' AS CATEGORY "
                    + " FROM BGM_PROJECT_OPERATION_EXPENSE poe "
                        + " INNER JOIN BGM_PROJECT_OPERATION po ON poe.BGM_PROJ_OPER_SEQ = po.BGM_PROJ_OPER_SEQ"
                        + " INNER JOIN BGM_PROJECT_EXPENSE pe ON poe.BGM_PROJ_EXP_SEQ = pe.BGM_PROJ_EXP_SEQ "
                        + " INNER JOIN BGM_MAS_EXPENSE ex ON pe.BGM_EXP_SEQ = ex.BGM_EXP_SEQ "
                        + " INNER JOIN BGM_MAS_SOURCE s ON pe.BGM_SOURCE_SEQ = s.BGM_SOURCE_SEQ "
                        + " INNER JOIN ICM_MAS_PURCHASE_RULE_MAPPING rm ON pe.BGM_SOURCE_SEQ = rm.BGM_SOURCE_SEQ "
                    + " WHERE po.RECORD_STATUS = @approve_status"
                        + " AND po.BGM_YEAR = @bgm_year"
                        + " AND po.JOB_SITE_SEQ = @job_site_seq"
                    + " UNION ALL "
                    + " SELECT CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-', pe.BGM_SOURCE_SEQ , '-' , poe.BGM_PROJ_OPER_SEQ, '-' , poe.BGM_PROJ_OPER_EXP_SEQ, '-' , i.ICM_RES_ITEM_SEQ) AS CHAR) AS ID "
                        + " , CAST(CONCAT(rm.ICM_PC_RULE_SEQ, '-', pe.BGM_SOURCE_SEQ , '-' , poe.BGM_PROJ_OPER_SEQ, '-' , poe.BGM_PROJ_EXP_SEQ) AS CHAR) AS PARENT_ID "
                        + " , i.ICM_RES_ITEM_NAME AS NAME "
                        + " , i.ICM_RES_ITEM_DESCR AS DESCR "
                        + " , 'ITEM' AS CATEGORY "
                    + " FROM BGM_PROJECT_OPERATION_EXPENSE poe "
                        + " INNER JOIN BGM_PROJECT_OPERATION po ON  poe.BGM_PROJ_OPER_SEQ = po.BGM_PROJ_OPER_SEQ"
                        + " INNER JOIN BGM_PROJECT_EXPENSE pe ON poe.BGM_PROJ_EXP_SEQ = pe.BGM_PROJ_EXP_SEQ "
                        + " INNER JOIN ICM_MAS_RESOURCE_ITEM i ON pe.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ "
                        + " INNER JOIN ICM_MAS_PURCHASE_RULE_MAPPING rm ON pe.BGM_SOURCE_SEQ = rm.BGM_SOURCE_SEQ"
                    + " WHERE po.RECORD_STATUS = @approve_status"
                        + " AND po.BGM_YEAR = @bgm_year"
                        + " AND po.JOB_SITE_SEQ = @job_site_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = (dr[i]["DESCR"]).ToString();
                        info.Category = (dr[i]["CATEGORY"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<TreeInfo> GetAllBgmSourceTree()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT CAST(BGM_SOURCE_TYPE_SEQ AS CHAR) AS ID"
                            + ", NULL AS PARENT_ID"
                            + ", BGM_SOURCE_TYPE_NAME AS NAME"
                            + ", BGM_SOURCE_TYPE_DESCR AS DESCR"
                        + " FROM BGM_MAS_SOURCE_TYPE"
                        + " WHERE RECORD_STATUS <> @delete_status"
                        + " UNION ALL"
                        + " SELECT DISTINCT CAST(CONCAT(BGM_SOURCE_TYPE_SEQ , '-' , BGM_SOURCE_SEQ) AS CHAR) AS ID"
                            + ", CASE WHEN UPPER_BGM_SOURCE_SEQ IS NULL THEN CAST(BGM_SOURCE_TYPE_SEQ AS CHAR)"
                                + " ELSE CAST(CONCAT(BGM_SOURCE_TYPE_SEQ , '-', UPPER_BGM_SOURCE_SEQ) AS CHAR) END AS PARENT_ID"
                            + ", BGM_SOURCE_NAME AS NAME"
                            + ", BGM_SOURCE_DESCR AS DESCR"
                        + " FROM BGM_MAS_SOURCE"
                        + " WHERE RECORD_STATUS <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = (dr[i]["DESCR"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<TreeInfo> GetBgmSourceTreeWithRule(int rule_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeInfo> infoList = new List<TreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@rule_seq", rule_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT CAST(BGM_SOURCE_TYPE_SEQ AS CHAR) AS ID"
                            + ", NULL AS PARENT_ID"
                            + ", BGM_SOURCE_TYPE_NAME AS NAME"
                            + ", BGM_SOURCE_TYPE_DESCR AS DESCR"
                            + ", 0 AS CHECKED"
                        + " FROM BGM_MAS_SOURCE_TYPE"
                        + " WHERE RECORD_STATUS <> @delete_status"
                        + " UNION ALL"
                        + " SELECT DISTINCT CAST(CONCAT(BGM_SOURCE_TYPE_SEQ , '-' , BGM_SOURCE_SEQ) AS CHAR) AS ID"
                            + ", CASE WHEN UPPER_BGM_SOURCE_SEQ IS NULL THEN CAST(BGM_SOURCE_TYPE_SEQ AS CHAR)"
                                + " ELSE CAST(CONCAT(BGM_SOURCE_TYPE_SEQ , '-', UPPER_BGM_SOURCE_SEQ) AS CHAR) END AS PARENT_ID"
                            + ", BGM_SOURCE_NAME AS NAME"
                            + ", BGM_SOURCE_DESCR AS DESCR"
                            + ", CASE WHEN (select count(*)"
                                + " from icm_mas_purchase_rule_mapping"
                                + " where icm_pc_rule_seq = @rule_seq"
                                    + " and bgm_source_seq = BGM_MAS_SOURCE.BGM_SOURCE_SEQ) > 0"
                            + " THEN 1 "
                            + " ELSE 0 END AS CHECKED"
                        + " FROM BGM_MAS_SOURCE"
                        + " WHERE RECORD_STATUS <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeInfo info = new TreeInfo();
                        info.Id = (dr[i]["ID"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = (dr[i]["DESCR"]).ToString();
                        info.Check = Convert.ToInt32(dr[i]["CHECKED"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
    }
}
